package co.th.bjc.md.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.bjc.md.common.ConnectDB;
import co.th.bjc.md.model.Product;

public class ProductHelper {

private static Logger _log = Logger.getLogger(ProductHelper.class);
	
	public List<Product> findProductByCategoryAndBrand(BigDecimal category, BigDecimal brand) throws Exception{
		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<Product> listProduct = new ArrayList<Product>(0);
		ConnectDB connectDB = null;
		try{
			String sql = "SELECT * FROM product WHERE category = ? AND brand = ?";
			_log.info("SQL : " + sql);
			
			connectDB = new ConnectDB();
			conn = connectDB.getConnection();
			stmt = conn.prepareStatement(sql);
			stmt.setBigDecimal(1, category);
			stmt.setBigDecimal(2, brand);
			
			rs = stmt.executeQuery();
			while (rs.next()){
				listProduct.add(this.setProduct(rs));
			}
		}catch(Exception ex){
			_log.error("findProductByCategoryAndBrand --> " + ex.getMessage());
			ex.printStackTrace();
		}finally{
			if (rs != null) rs.close();
			if (stmt != null) stmt.close();
			connectDB.close();
		}
		return listProduct;
	}
	
	private Product setProduct(ResultSet rs) throws Exception{
		Product product = new Product();
		product.setProductCode(rs.getBigDecimal("product_code"));
		product.setProductYear(rs.getBigDecimal("product_year"));
		product.setProductNameTh(rs.getString("product_name_th"));
		product.setProductNameEn(rs.getString("product_name_en"));
		product.setProductDetail(rs.getString("product_detail"));
		product.setImage(rs.getBlob("image"));
		product.setCategory(new CategoryHelper().findCategoryByCategoryCode(rs.getBigDecimal("category")));
		product.setBrand(new BrandHelper().findBrandByBrandCode(rs.getBigDecimal("brand")));
		product.setSupplier(null);
		product.setActive(rs.getString("active"));
		product.setCreateBy(rs.getString("create_by"));
		product.setCreateDate(rs.getDate("create_date"));
		product.setUpdateBy(rs.getString("update_by"));
		product.setUpdateDate(rs.getDate("update_date"));
		return product;
	}
}
